package com.xying.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletResponse;

import lombok.extern.slf4j.Slf4j;

/**
 * @author: Hisen
 * @date: 2018/8/15 8:40
 * @description:
 */
@Slf4j
public class ExcelUtil {
  private static final Pattern NUMBER_PATTERN = Pattern.compile("^//d+(//.//d+)?$");

  public static void exportExcel(HSSFWorkbook workbook, int num, String title, String[] headers, List<Object[]> dataset) {
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet();
    // 设置表格名称
    workbook.setSheetName(num, title);
    // 设置表格默认列宽度为20个字节
    sheet.setDefaultColumnWidth(25);
    // 生成一个样式
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置这些样式
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // 把字体应用到当前的样式
    style.setFont(font);
    /*// 生成并设置另一个样式
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 生成另一个字体
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // 把字体应用到当前的样式
    style2.setFont(font2);*/
    // 声明一个画图的顶级管理器
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // 定义注释的大小和位置,详见文档
    HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
            0, 0, 0, (short) 4, 2, (short) 6, 5));
    // 设置注释内容
    comment.setString(new HSSFRichTextString("传感器数据"));
    // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
    comment.setAuthor("Hisen");
    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    for (int i = 0; i < headers.length; i++) {
      HSSFCell cell = row.createCell(i);
      cell.setCellStyle(style);
      HSSFRichTextString text = new HSSFRichTextString(headers[i]);
      cell.setCellValue(text);
    }
    // 遍历集合数据，产生数据行
    int index = 0;
    for (Object[] o : dataset) {
      index++;
      row = sheet.createRow(index);
      for (int i = 0; i < o.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);
        try {
          Object value = o[i];
          if(value== null){
            value="";
          }
          // 判断值的类型后进行强制类型转换
          String textValue = null;
          if (value instanceof Boolean) {
            boolean bValue = (Boolean) value;
            textValue = "已开启";
            if (!bValue) {
              textValue = "已停用";
            }
          } else if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat(
                    "yyyy-MM-dd HH:mm:ss");
            textValue = sdf.format(date);
          } else if (value instanceof byte[]) {
            // 有图片时，设置行高为160px;
            row.setHeightInPoints(160);
            // 设置图片所在列宽度为80px,注意这里单位的一个换算
            sheet.setColumnWidth(i, (int) (35.7 * 320));
            byte[] bsValue = (byte[]) value;
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                    1023, 255, (short) i, index, (short) i, index);
            anchor.setAnchorType(3);
            patriarch.createPicture(anchor, workbook.addPicture(
                    bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
          } else {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
          }
          // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
          if (textValue != null) {
            HSSFRichTextString richString = new HSSFRichTextString(
                    textValue);
            richString.applyFont(font);
            cell.setCellValue(richString);
          }
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
  }

  public static void exportExcel(HttpServletResponse response, String[] headArray,
                                 List<Object[]> contentList, String fileName) throws Exception {
    //创建流
    InputStream is;
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 读到流中
    exportExcel(workbook, 0, fileName, headArray, contentList);

    try {
      workbook.write(os);
    } catch (IOException e) {
      e.printStackTrace();
    }
    is = new ByteArrayInputStream(os.toByteArray());
    write(response, is, fileName);
  }

  /**
   * 导出设备的所有传感器数据，数据按照传感器分表
   * @param response
   * @param headArray
   * @param content
   * @param fileName
   * @throws Exception
   */
  public static void exportDeviceExcel(HttpServletResponse response, String[] headArray,
                                       List<List<Object[]>> content, List<String> sheetNameList, String fileName) throws Exception {
    //创建流
    InputStream is;
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 读到流中
    for (int i = 0; i < content.size(); i++) {
      List<Object[]> contentList = content.get(i);
      exportExcel(workbook, i, sheetNameList.get(i), headArray, contentList);
    }

    try {
      workbook.write(os);
    } catch (IOException e) {
      e.printStackTrace();
    }
    is = new ByteArrayInputStream(os.toByteArray());

    write(response, is, fileName);
  }

  public static void write(HttpServletResponse response, InputStream inStream, String fileName) throws UnsupportedEncodingException {
    // 设置输出的格式
    response.reset();
    response.setCharacterEncoding("UTF-8");
    response.setContentType("application/x-msdownload");
    response.setHeader("Content-Disposition", "attachment; filename="
            + URLEncoder.encode(fileName, "UTF-8"));
    // 循环取出流中的数据
    byte[] b = new byte[100];
    int len;
    try {
      while((len = inStream.read(b)) > 0) {
        response.getOutputStream().write(b, 0, len);
      }
      inStream.close();
    } catch (IOException e) {
      log.error("导出Excel错误", e);
    }
  }
}
